package com.iceberg.dao.impl.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang3.math.NumberUtils;

import com.iceberg.dao.database.DatabaseDAO;
import com.iceberg.model.database.Column;
import com.iceberg.model.database.Schema;
import com.iceberg.model.database.Table;
import com.iceberg.model.database.View;
import com.iceberg.model.jqgrid.JqGridTable;

public class DatabaseJdbcDAOImpl implements DatabaseDAO {
  //TODO log info using slf logger
  DataSource dataSource;

  public DataSource getDataSource() {
    return dataSource;
  }

  public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
  }

  @Override
  public List<Schema> getSchemaList(String databaseName) {
    List<Schema> schemaList = new ArrayList<Schema>();
    Connection connection = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        ResultSet rs = connection.getMetaData().getCatalogs();

        while (rs.next()) {
          String schemaName = rs.getString("TABLE_CAT");
          Schema schema = new Schema();
          schema.setId(schemaName);
          schema.setName(schemaName);
          schemaList.add(schema);
        }
      }
    } catch (SQLException ex) {
      // TODO Auto-generated catch block
      ex.printStackTrace();
    } finally {
      try {
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return schemaList;
  }

  @Override
  public List<Table> getTableList(String catalogName, String schemeName) {
    List<Table> tableList = new ArrayList<Table>();
    Connection connection = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        DatabaseMetaData dbmd = connection.getMetaData();
        String[] types = {"TABLE" };
        ResultSet rs = dbmd.getTables(schemeName, schemeName, "%", types);

        while (rs.next()) {
          String tableCatalog = rs.getString(1);
          String tableSchema = rs.getString(2);
          String tableName = rs.getString(3);

          Table table = new Table();
          table.setId(tableName);
          table.setName(tableName);
          tableList.add(table);
        }
      }
    } catch (SQLException ex) {
      // TODO Auto-generated catch block
      ex.printStackTrace();
    } finally {
      try {
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return tableList;
  }

  @Override
  public List<View> getViewList(String catalogName, String schemeName) {
    List<View> viewList = new ArrayList<View>();
    Connection connection = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        DatabaseMetaData dbmd = connection.getMetaData();
        String[] types = {"VIEW" };
        ResultSet rs = dbmd.getTables(schemeName, schemeName, "%", types);
        while (rs.next()) {
          String tableCatalog = rs.getString(1);
          String tableSchema = rs.getString(2);
          String tableName = rs.getString(3);

          View table = new View();
          table.setId(tableName);
          table.setName(tableName);
          viewList.add(table);
        }
      }
    } catch (SQLException ex) {
      // TODO Auto-generated catch block
      ex.printStackTrace();
    } finally {
      try {
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return viewList;
  }

  @Override
  public List<Column> getColumnList(String catalogName, String schemaName, String tableName) {
    List<Column> columnList = new ArrayList<Column>();
    columnList = getColumnsMethod2(catalogName, schemaName, tableName);
    return columnList;
  }

  private List<Column> getColumnsMethod2(String catalogName, String schemaName, String tableName) {
    List<Column> columnList = new ArrayList<Column>();
    Connection connection = null;
    Statement stmt = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        stmt = connection.createStatement();
        String queryString = "select * from " + tableName;
        ResultSet rs = stmt.executeQuery(queryString);
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 1; rsmd != null && i < rsmd.getColumnCount() + 1; i++) {

          String columnName = rsmd.getColumnName(i);
          String columnType = rsmd.getColumnTypeName(i);
          String columnClassName = rsmd.getColumnClassName(i);
          String columnLabel = rsmd.getColumnLabel(i);

          Column column = new Column();

          column.setId(columnName);
          column.setName(columnName);
          column.setColumnClassName(columnClassName);
          column.setColumnLabel(columnLabel);
          column.setColumnName(columnName);
          column.setColumnType(columnType);
          int isNullable = rsmd.isNullable(i);
          column.setIsNullable(isNullable == rsmd.columnNullable);

          columnList.add(column);
        }

      }
    } catch (SQLException ex) {
      // TODO Auto-generated catch block
      ex.printStackTrace();
    } finally {
      try {
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return columnList;
  }

  private List<Column> getColumnsMethod1(String catalogName, String schemaName, String tableName) {
    List<Column> columnList = new ArrayList<Column>();
    Connection connection = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        DatabaseMetaData dbmd = connection.getMetaData();
        ResultSet rs = dbmd.getColumns(catalogName, schemaName, tableName, "%");
        while (rs.next()) {
          String columnName = rs.getString("COLUMN_NAME");
          Column column = new Column();
          column.setId(columnName);
          column.setName(columnName);
          columnList.add(column);
        }
      }
    } catch (SQLException ex) {
      // TODO Auto-generated catch block
      ex.printStackTrace();
    } finally {
      try {
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return columnList;
  }

  @Override
  public  JqGridTable  getTableData(String tableName, JqGridTable jqGridTable) {
    int pageNumber = jqGridTable.getCurrentPageNumber();
    int recordSizePerPage = jqGridTable.getRecordSizePerPage();
    
    int startIndex = (pageNumber - 1) * recordSizePerPage + 1;
    int endIndex = startIndex + recordSizePerPage;

    List<HashMap<String, String>> dataList = new ArrayList<HashMap<String, String>>();
    jqGridTable.setRawTableData(dataList);
    
    List<Column> columnList = getColumnsMethod2("", "", tableName);
    Connection connection = null;
    Statement stmt = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String queryString = "select * from " + tableName;
        ResultSet rs = stmt.executeQuery(queryString);
        if(rs == null) {
          return jqGridTable;
        }
        rs.last();

        int rownum = rs.getRow();
        //set the total records number
        jqGridTable.setTotalRecordSize(rownum);
        int totalPageSize = jqGridTable.getTotalRecordSize() / recordSizePerPage;
        jqGridTable.setTotalPageSize(totalPageSize);
     

        if (startIndex + 1 > rownum) {
          return jqGridTable;
        }
        int startnum = startIndex;
        int endnum = endIndex + 1;
        if (rownum < endnum) {
          endnum = rownum;
        }

        rs.absolute(startnum);

        while (rs.next()) {
          if (columnList != null) {
            HashMap<String, String> rowData = new HashMap<String, String>();
            for (Column column : columnList) {
              String columnName = column.getColumnName();
              String columnValue = rs.getString(columnName);
              rowData.put(columnName, columnValue);
            }
            dataList.add(rowData);
          }
        }
      }
    } catch (SQLException ex) {
      // TODO Auto-generated catch block
      ex.printStackTrace();
    } finally {
      try {
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return jqGridTable;
  }

  @Override
  public Boolean dataUpdate(String tableName, Map<String, Object> parameters) {

    Table table = this.getTable(null, null, tableName);

    List<Column> columns = table.getColumnList();

    String updateSqlString = "";
    StringBuffer updateStringBuffer = new StringBuffer(" UPDATE ").append(tableName);
    updateStringBuffer.append(" set ");
    for (Column column : columns) {
      updateStringBuffer.append(column.getColumnName() + "=?,");
    }
    updateStringBuffer.deleteCharAt(updateStringBuffer.length() - 1);
    updateStringBuffer.append(" where " + table.getPrimaryKeyColumn().getColumnName() + "=?");
    updateSqlString = updateStringBuffer.toString();
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        preparedStatement = connection.prepareStatement(updateSqlString);
        String customerId = "";
        int index = 1;
        for (Column column : columns) {
          String key = column.getColumnName();
          String type = column.getColumnType();
          Object value = parameters.get(key);
          String stringValue = null;
          if (value instanceof String[]) {
            stringValue = ((String[]) value)[0];
          }
          else {
            stringValue = (String) value;
          }

          if (value != null) {
            if (type.equals("VARCHAR")) {
              preparedStatement.setString(index, stringValue);
            }
            else if (type.equals("INT")) {
              if (key.equalsIgnoreCase("CUSTOMER_ID")) {
                customerId = stringValue;
              }
              if (NumberUtils.isNumber(stringValue)) {
                Integer intValue = new Integer(stringValue);
                preparedStatement.setInt(index, intValue);
              }
            }
            else if (type.equals("TIMESTAMP")) {
              //TODO change it
              preparedStatement.setTimestamp(index, new Timestamp(new Date().getTime()));
            }
          }
          index++;
        }
        preparedStatement.setString(index, customerId);
        int count = preparedStatement.executeUpdate();
        return count > 0;

      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        if (preparedStatement != null) {
          preparedStatement.close();
        }
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return false;
  }

  @Override
  public Boolean dataCreate(String tableName, Map<String, Object> parameters) {
    /**
     * insert into tablename(col1,col2) values (val1,val2);
     */
    Table table = this.getTable(null, null, tableName);
    //TODO auto insert primaryKey value
    String primaryKeyValue = table.getPrimaryKeyColumn().getColumnName();
    List<Column> columns = table.getColumnList();

    String createSqlString = "";
    StringBuffer createStringBuffer = new StringBuffer(" insert into ").append(tableName);
    createStringBuffer.append("(");
    for (Column column : columns) {
      createStringBuffer.append(column.getColumnName() + ",");
    }
    createStringBuffer.deleteCharAt(createStringBuffer.length() - 1);
    createStringBuffer.append(") values (");
    for (Column column : columns) {
      Object object = parameters.get(column.getColumnName().toString());
      if (object != null && object instanceof String[]) {
        String[] objStrings = (String[]) object;
        if (objStrings.length > 0) {
          String value = objStrings[0];
          if (value.equals("")) {
            createStringBuffer.append("null,");
          }
          else {
            createStringBuffer.append(value + ",");
          }
        }
        else {
          createStringBuffer.append("null,");
        }
      }
    }
    createStringBuffer.deleteCharAt(createStringBuffer.length() - 1);
    createStringBuffer.append(")");

    createSqlString = createStringBuffer.toString();
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        preparedStatement = connection.prepareStatement(createSqlString);
        int count = preparedStatement.executeUpdate();
        return count > 0;
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        if (preparedStatement != null) {
          preparedStatement.close();
        }
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return false;
  }

  @Override
  public Boolean dataDelete(String tableName, Map<String, Object> parameters) {
    /**
     * delete from tablename where primarykey = '';
     */

    Table table = this.getTable(null, null, tableName);
    String primaryKeyValue = table.getPrimaryKeyColumn().getColumnName();
    String deleteSqlString = "";
    StringBuffer deleteStringBuffer = new StringBuffer(" delete from ").append(tableName);
    deleteStringBuffer.append(" where " + primaryKeyValue + " in( ? )");
    deleteSqlString = deleteStringBuffer.toString();
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        preparedStatement = connection.prepareStatement(deleteSqlString);

        //TODO id is the customized by Jqgrid
        String[] primaryKeyValueArray = (String[]) parameters.get(primaryKeyValue);
        String primaryKeyValues = "";
        if (primaryKeyValueArray != null && primaryKeyValueArray.length == 1) {
          //apache commons lang3
          primaryKeyValues = primaryKeyValueArray[0];
          primaryKeyValueArray = primaryKeyValues.split(",");
          for (String id : primaryKeyValueArray) {
            preparedStatement.setString(1, id);
            preparedStatement.addBatch();
          }
        }

        int[] count = preparedStatement.executeBatch();
        return count != null && count.length > 0;
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        if (preparedStatement != null) {
          preparedStatement.close();
        }
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return false;
  }

  @Override
  public Table getTable(String catalogName, String schemaName, String tableName) {
    Connection connection = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        DatabaseMetaData dbmd = connection.getMetaData();
        String[] types = {"TABLE" };
        ResultSet rs = dbmd.getTables(catalogName, schemaName, "%", types);

        while (rs.next()) {
          String tableCatalog = rs.getString(1);
          String tableSchema = rs.getString(2);
          String tempTableName = rs.getString(3);

          if (tempTableName.equals(tableName)) {
            Table table = new Table();
            table.setId(tableName);
            table.setName(tableName);
            List<Column> columns = this.getColumnList(catalogName, schemaName, tempTableName);
            List<Column> primaryKeyColums = this.getPrimaryKeyColumnList(catalogName, schemaName, tempTableName);
            table.setColumnList(columns);
            table.setPrimaryKeyColumnList(primaryKeyColums);
            if (primaryKeyColums != null && primaryKeyColums.size() > 0) {
              table.setPrimaryKeyColumn(primaryKeyColums.get(0));
            }
            return table;
          }
        }
      }
    } catch (SQLException ex) {
      // TODO Auto-generated catch block
      ex.printStackTrace();
    } finally {
      try {
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return null;
  }

  @Override
  public List<Column> getPrimaryKeyColumnList(String catalogName, String schemaName, String tableName) {
    List<Column> columnList = new ArrayList<Column>();
    Connection connection = null;
    try {
      if (dataSource != null) {
        connection = dataSource.getConnection();
        DatabaseMetaData dbmd = connection.getMetaData();
        ResultSet rs = dbmd.getPrimaryKeys(catalogName, schemaName, tableName);
        ResultSetMetaData rsmd = rs.getMetaData();
        int cols = rsmd.getColumnCount();

        while (rs.next()) {
          for (int i = 1; i <= cols; i++) {
            System.out.println(rs.getString(i));
          }

          String columnName = rs.getString("COLUMN_NAME");
          Column column = new Column();

          column.setId(columnName);
          column.setName(columnName);
          column.setColumnName(columnName);
          columnList.add(column);
        }
      }
    } catch (SQLException ex) {
      // TODO Auto-generated catch block
      ex.printStackTrace();
    } finally {
      try {
        connection.close();
      } catch (SQLException ex) {
        // TODO Auto-generated catch block
        ex.printStackTrace();
      }
    }
    return columnList;
  }

  @Override
  public List<Column> getForeignKeyColumnList(String catalogName, String schemaName, String tableName) {
    //TODO Auto-generated method stub
    throw new IllegalStateException("liyang didn't do his work!");
  }

  @Override
  public List<Column> getReferencedForeignKeyColumnList(String catalogName, String schemaName, String tableName) {
    //TODO Auto-generated method stub
    throw new IllegalStateException("liyang didn't do his work!");
  }

}
